﻿'Description:工时类接口
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yangsj
'create Date:2017-6-08
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient

Public Class uLMSStepTimeBusiness
    Implements uLMSStepTimeIBusiness

    ''' <summary>
    ''' 获取工艺工序工时
    ''' </summary>
    Public Function GetWorkflowStepTimeData(ByVal strWorkflowID As String) As System.Data.DataTable Implements IBusiness.uLMSStepTimeIBusiness.GetWorkflowStepTimeData
        Dim sb As New StringBuilder
        'modify tianFW 2017/09/11
        'Modify by Yuxj20181031 增加wf.workflowtype,wf.ly2modle_node 装试施工工艺显示发动机号
        sb.AppendLine("SELECT wf.workflowtype,wf.ly2modle_node, WFS.WORKFLOWSTEPNAME,WFS.NOTES,round(STD.DURATIONPERUNIT,3) DURATIONPERUNIT,WFS.WORKFLOWSTEPID,WFS.WORKCENTERID,WC.WORKCENTERNAME,")
        sb.AppendLine("WFS.TEAMID,T.TEAMNAME,round(WFS.MANHOURS,3) MANHOURS,sio.stepno||'-'||sio.description stepinfodesc,sio.durationperunit stepinfodura,sio.durationperunit2,")
        sb.AppendLine("sio.stepinfoid,sib.stepinfoname,sio.notes stepinfonotes,em.fullname CheckTechnique,em.employeeid CheckTechniqueID")
        'setuptime add by tianF 2018年9月4日
        'add by tianFW 2018年7月4日 增加工序spec的检验序或外委或转工信息查询对比数据
        sb.AppendLine(",s.lastcheck,s.controlcode,wf.factoryid wffactoryid,s.factoryid sfactoryid,round(STD.setuptime,3) setuptime")

        'Modify by Yuxj20181127 添加默认工步Id
        sb.AppendLine(",sib.revofrcdid,WFS.Preparecycle,WFS.Makecycle,WFS.Transfercycle,WFS.Shipcycle,WFS.Commicycle ")
        sb.AppendLine("FROM WORKFLOWSTEP WFS")
        sb.AppendLine("LEFT JOIN Workflow wf ON wfs.workflowid = wf.workflowid") '增加工艺工艺员查询 2018年6月21日 em.fullname CheckTechnique
        sb.AppendLine("LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid")
        sb.AppendLine("LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,wfs.specid)")
        sb.AppendLine("LEFT JOIN employee em ON em.employeeid = wf.zgemployeeid")
        sb.AppendLine("LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = WFS.WORKCENTERID")
        sb.AppendLine("LEFT JOIN TEAM T ON T.TEAMID = WFS.TEAMID")
        sb.AppendLine("LEFT JOIN STEPSCHEDULINGDETAIL STD ON STD.STEPID = WFS.WORKFLOWSTEPID")
        sb.AppendLine("LEFT JOIN stepinfo sio ON sio.workflowstepid = wfs.workflowstepid")
        'Modify by Yuxj20181127 工步有多个版本时取默认版本
        'sb.AppendLine("LEFT JOIN stepinfobase sib ON sib.stepinfobaseid = sio.stepinfobaseid")
        sb.AppendLine("LEFT JOIN stepinfobase sib ON sio.stepinfoid=sib.revofrcdid ")

        sb.AppendFormat(" WHERE WFS.WORKFLOWID = '{0}' AND WFS.SEQUENCE IS NOT NULL", strWorkflowID)
        sb.AppendLine(" ORDER BY WFS.SEQUENCE")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 更新工时
    ''' </summary>
    ''' <param name="dtstep"></param>
    ''' <param name="type">0 更新工时和备注 1 更新工区和班组</param>
    ''' <returns></returns>
    ''' <remarks> modify by tianFW 2018年7月3日</remarks>
    Public Function UpdateStepTimeInfo(ByVal dtstep As DataTable, ByVal type As Integer) As Boolean Implements uLMSStepTimeIBusiness.UpdateStepTimeInfo

        Dim SQLlist As New Dictionary(Of String, OracleParameter())()
        Dim strSQL As String
        If type = 0 Then
            For Each dr As DataRow In dtstep.Rows
                strSQL = String.Format("UPDATE stepschedulingdetail sdt set sdt.durationperunit='{0}' ", dr("durationperunit"))
                If dtstep.Columns.Contains("setuptime") Then
                    strSQL += String.Format(",sdt.setUpTime='{0}'", dr("setuptime"))
                End If
                strSQL += String.Format(" where sdt.stepid='{0}'", dr("workflowstepid"))
                '添加到哈希表
                SQLlist.Add(strSQL, Nothing)


                If dtstep.Columns.Contains("note") Then
                    strSQL = String.Format("UPDATE Workflowstep wfs SET wfs.notes='{0}' WHERE wfs.workflowstepid='{1}'",
                                    dr("note"), dr("workflowstepid"))
                    '添加到哈希表
                    SQLlist.Add(strSQL, Nothing)
                End If
            Next
        Else
            For Each dr As DataRow In dtstep.Rows
                strSQL = String.Format("UPDATE Workflowstep wfs SET wfs.workcenterid='{0}',wfs.teamid='{1}' WHERE wfs.workflowstepid='{2}'",
                                      dr("workCenterID"), dr("teamID"), dr("workflowstepid")) 'modify 在原基础上添加了工区ID和班组ID
                '添加到哈希表
                SQLlist.Add(strSQL, Nothing)
            Next
        End If
        DatabaseHelper.ExecuteSqlTranByHash(SQLlist)
        UpdateStepTimeInfo = True
    End Function

    ''' <summary>
    ''' 更新装试工步工时 create by tianFW 2018年5月29日
    ''' </summary>
    ''' <param name="StepInfoID"></param>
    ''' <param name="Value">初装工时</param>
    ''' <param name="Value2">检装工时</param>
    ''' <param name="notes">工步备注</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateZSStepInfoDurationUnit(ByVal StepInfoID As String, ByVal Value As String, ByVal Value2 As String, ByVal notes As String) As Boolean Implements uLMSStepTimeIBusiness.UpdateZSStepInfoDurationUnit

        Dim keyValue As New Object
        Dim strsql = "UPDATE stepinfo sio SET sio.durationperunit=:durationperunit,sio.durationperunit2=:durationperunit2,sio.notes=:notes WHERE sio.stepinfoid=:stepinfoid"
        Dim oraclepara(3) As OracleParameter
        GetDataValue(Value, keyValue)
        oraclepara(0) = DatabaseHelper.NewParameter(":durationperunit", keyValue, OracleType.Number)
        GetDataValue(Value2, keyValue)
        oraclepara(1) = DatabaseHelper.NewParameter(":durationperunit2", keyValue, OracleType.Number)
        GetDataValue(notes, keyValue)
        oraclepara(2) = DatabaseHelper.NewParameter(":notes", keyValue, OracleType.VarChar)
        oraclepara(3) = DatabaseHelper.NewParameter(":stepinfoid", StepInfoID, OracleType.VarChar)
        If DatabaseHelper.ExecuteSql(strsql, oraclepara) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Sub GetDataValue(ByVal value As Object, ByRef keyValue As Object)
        If value = "" Then
            keyValue = DBNull.Value
        Else
            keyValue = value
        End If
    End Sub

    ''' <summary>
    ''' 获取工时
    ''' Create：YangSJ
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkTimeData(ByVal para As Dictionary(Of String, String), ByRef totalStepTimeValue As Double) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetWorkTimeData
        Dim sb As New StringBuilder
        If para.Keys.Contains("ReportBdate") Or para.Keys.Contains("ReportEdate") Then '将根据报工时间查询修改为根据检验时间查询
            sb.AppendLine("SELECT * FROM (")
        End If
        sb.AppendLine("SELECT CSTS.CONTAINERNAME,C.CONTAINERID,C.PRODUCTNAME,C.PRODUCTID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,NVL(CSTS.DURATIONPERUNIT,0) WORKDURATION,")
        sb.AppendLine("NVL(STD.DURATIONPERUNIT,0) STEPDURATION,CSTS.WORKFLOWSTEPNAME,CSTS.SCRAPQTY,(CSTS.QTY- CSTS.SCRAPQTY) ACCEPTANCEQTY,CSTS.VERIFICATIONDATE,CSTS.ADJUSTDATE,")
        'modify  by tianFW 2018年6月8日 单位工时和总工时使用查询，屏蔽下面的逻辑处理，
        '不再查询NVL(CSTSD.SPLITDURATION,-1) SPLITDURATION 
        Dim valuestr As String =
            <string>
                CASE
			    WHEN (csts.verificationdate IS NULL AND csts.adjustdate IS NULL) THEN
					nvl(std.durationperunit, 0)
				ELSE
					nvl(csts.durationperunit, 0)
				END durationperunit,
				CASE
				WHEN cstsd.splitemployeeid IS NOT NULL THEN
					 nvl(cstsd.splitduration, -1)
				WHEN (csts.verificationdate IS NULL AND csts.adjustdate IS NULL) THEN
					 round((csts.qty - csts.scrapqty) *(nvl(std.durationperunit, 0)) / nvl(csts.splitcount, 1),3)
				ELSE csts.totalduration
				END totalduration,
            </string>
        sb.AppendLine(valuestr)
        sb.AppendLine("CSTS.QTY,CSTS.WORKERID,T.TEAMNAME,WC.WORKCENTERNAME,S.SPECNO,CSTS.CREATEDATE,E.FULLNAME WORKER,E.EMPLOYEENAME WORKERCODE,EA.FULLNAME ADJUST,")
        'add WS.NOTES by tianFW 2018年5月31日-- add std.setuptime 2018年9月4日准备工时
        sb.AppendLine("EA.EMPLOYEENAME ADJUSTCODE,EV.FULLNAME VERIFICATION,EV.EMPLOYEENAME VERIFICATIONCODE,CSTS.SUBMITDATE,WS.NOTES,")
        'add tianfw 添加拆分人员的ES.FULLNAME SPLITWORKER,ES.EMPLOYEENAME SPLITEMPLOYEENAME 以及拆分数NVL(CSTS.SPLITCOUNT,1) SPLITCOUNT
        'remark by tianFW 2018年6月8日 NVL(CSTSD.SPLITDURATION,-1) 修改为-1，可能最后拆分时CSTSD.SPLITDURATION就是0
        sb.AppendLine("CSTS.CONTAINERSTEPTIMESHEETID,C.FAMILYLISTNAMES,ES.FULLNAME SPLITFULLNAME,ES.EMPLOYEENAME SPLITEMPLOYEENAME,NVL(CSTS.SPLITCOUNT,1) SPLITCOUNT")
        If para.Keys.Contains("ReportBdate") Or para.Keys.Contains("ReportEdate") Then
            sb.AppendLine(",NVL(CFI.CREATEDATE, CFSI.CREATEDATE) CHECKDATE")
        End If
        If para.Keys.Contains("SetUpTime") Then
            sb.AppendLine(",std.setuptime,")
            valuestr =
                <string>
                    CASE WHEN csrk.usecompleted = 0 THEN
                    0
                    WHEN cstsd.splitemployeeid IS NOT NULL THEN
			        --准备工时/总合格数*合格数*(拆分工时/(合格数*单位工时))
			        nvl(round((std.setuptime / csrk.passqty) * (csts.qty - csts.scrapqty) * (nvl(cstsd.splitduration,0) / ((csts.qty - csts.scrapqty) * 
			        nvl(NVL(csts.durationperunit,std.durationperunit),0))),3),0)
			        WHEN (csts.verificationdate IS NULL AND csts.adjustdate IS NULL) THEN
			        --准备工时/总合格数*合格数/拆分数(拆分数一般为1)
			        nvl(round((std.setuptime / csrk.passqty) * (csts.qty - csts.scrapqty) / nvl(csts.splitcount, 1), 3),0)
			        END setuptimeduration
                </string>
            sb.AppendLine(valuestr)
        End If
        sb.AppendLine("FROM CONTAINERSTEPTIMESHEET CSTS ")
        sb.AppendLine("INNER JOIN CONTAINERCURRENTINFO C ON C.CONTAINERID = CSTS.CONTAINERID  ") 'modify by tianFW 2018年2月5日将产品信息直接在CONTAINERCURRENTINFO表中获取
        If para.Keys.Contains("ReportBdate") Or para.Keys.Contains("ReportEdate") Then
            sb.AppendLine("LEFT JOIN CONTAINERCHECKINFO CFI ON CFI.CONTAINERCHECKINFOID = CSTS.CONTAINERCHECKINFOID")
            sb.AppendLine("LEFT JOIN CONTAINERFINISHEDCHECKINFO CFSI ON CFSI.CONTAINERFINISHEDCHECKINFOID = CSTS.CONTAINERCHECKINFOID")
        End If
        If para.Keys.Contains("SetUpTime") Then
            sb.AppendLine("LEFT JOIN (SELECT csr.containerid,csr.workflowstepid,csr.usecompleted,SUM(csr.passqty) passqty")
            sb.AppendLine("FROM containersteprecord csr")
            sb.AppendLine("GROUP BY csr.containerid, csr.workflowstepid, csr.usecompleted) csrk")
            sb.AppendLine("ON csrk.containerid = csts.containerid and csrk.workflowstepid = csts.workflowstepid")
        End If
        sb.AppendLine("LEFT JOIN STEPSCHEDULINGDETAIL STD ON STD.STEPID = CSTS.WORKFLOWSTEPID  ")
        sb.AppendLine("LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = CSTS.WORKCENTERID  ")
        sb.AppendLine("LEFT JOIN TEAM T ON T.TEAMID = CSTS.TEAMID  ")
        sb.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSTS.WORKFLOWSTEPID")
        sb.AppendLine("LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID  ")
        sb.AppendLine("LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID) ")
        sb.AppendLine("LEFT JOIN EMPLOYEE E  ON E. EMPLOYEEID = CSTS.WORKERID ")
        sb.AppendLine("LEFT JOIN EMPLOYEE EA  ON EA. EMPLOYEEID = CSTS.ADJUSTEMPLOYEEID ")
        sb.AppendLine("LEFT JOIN EMPLOYEE EV  ON EV. EMPLOYEEID = CSTS.VERIFICATIONEMPLOYEEID ")
        sb.AppendLine("LEFT JOIN CONTAINERSTEPTIMESHEETSPLIT CSTSD  ON CSTSD.CONTAINERSTEPTIMESHEETID = CSTS.CONTAINERSTEPTIMESHEETID")
        sb.AppendLine("LEFT JOIN EMPLOYEE ES  ON ES. EMPLOYEEID =  CSTSD.WORKERID ") 'add tianFW 2018年3月21日 添加拆分记录的查询



        sb.AppendFormat(" WHERE (CSTS.QTY- CSTS.SCRAPQTY)>0 AND CSTS.FACTORYID='{0}' ", para("FactoryID"))


        If para.Keys.Contains("Status") Then
            If para("Status") = "1" Then '未提交
                sb.AppendLine(" AND CSTS.SUBMITDATE IS NULL ")
            ElseIf para("Status") = "2" Then '已提交
                sb.AppendLine(" AND CSTS.SUBMITDATE IS NOT NULL AND CSTS.ADJUSTDATE IS NULL AND CSTS.VERIFICATIONDATE IS NULL")
            ElseIf para("Status") = "3" Then '已核算
                sb.AppendLine(" AND CSTS.ADJUSTDATE IS NOT NULL ")
            ElseIf para("Status") = "4" Then '已核销
                sb.AppendLine(" AND CSTS.VERIFICATIONDATE IS NOT NULL ")
            End If
        End If
        If para.Keys.Contains("ProductFamilyName") Then
            If para("ProductFamilyName") = "WP13汇总" Then
                sb.AppendLine(" AND (C.familylistnames LIKE '%WP13%' OR C.familylistnames LIKE '%全通%' OR C.familylistnames LIKE '%13通%'")
                sb.AppendLine(" OR C.familylistnames LIKE '%WP13AII%' OR C.familylistnames LIKE '%WPFI%' OR C.familylistnames LIKE '%WPFII%')")
            ElseIf para("ProductFamilyName") = "WP7汇总" Then
                sb.AppendLine(" AND (C.familylistnames LIKE '%WP7%' OR  C.familylistnames LIKE '%7通%')")
            Else
                sb.AppendFormat(" AND C.familylistnames LIKE '%{0}%'", para("ProductFamilyName"))
            End If
        ElseIf para.Keys.Contains("ProductFamily") Then
            sb.AppendFormat(" AND C.FAMILYLISTIDS LIKE '%{0}%'", para("ProductFamily"))
        End If
        If para.Keys.Contains("WorkID") Then 'modify tianFW 将只从工时表查询人员，添加拆分表查询人员
            sb.AppendFormat(" AND NVL(CSTSD.WORKERID,CSTS.WORKERID)='{0}'", para("WorkID"))
        Else
            If para.Keys.Contains("WorkCenterID") Then
                sb.AppendFormat(" AND WC.WORKCENTERID ='{0}'", para("WorkCenterID"))
            End If
            If para.Keys.Contains("TeamID") Then
                sb.AppendFormat(" AND {0}", para("TeamID"))
            End If
        End If
        If para.Keys.Contains("StartDate") Then
            sb.AppendFormat(" AND CSTS.SUBMITDATE >TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("StartDate")))
        End If
        If para.Keys.Contains("EndDate") Then
            sb.AppendFormat(" AND CSTS.SUBMITDATE-1 <TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("EndDate")))
        End If
        If para.Keys.Contains("ProductName") Then
            sb.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sb.AppendFormat(" AND S.SPECNO = '{0}'", para("StepNo"))
        End If
        If para.Keys.Contains("ContainerName") Then
            sb.AppendFormat(" AND C.CONTAINERNAME LIKE '{0}%'", para("ContainerName"))
        End If
        sb.AppendLine("ORDER BY C.PRODUCTNAME,C.CONTAINERNAME,WS.SEQUENCE")
        If para.Keys.Contains("ReportBdate") Or para.Keys.Contains("ReportEdate") Then
            sb.AppendLine(") K WHERE")
        End If
        If para.Keys.Contains("ReportBdate") Then
            sb.AppendFormat(" NVL(K.CHECKDATE,K.CREATEDATE) >= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportBdate")))
        End If
        If para.Keys.Contains("ReportEdate") Then
            If para.Keys.Contains("ReportBdate") Then
                sb.AppendLine(" AND ")
            End If
            sb.AppendFormat(" NVL(K.CHECKDATE,K.CREATEDATE) - 1 <= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportEdate")))
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetWorkTimeData = DatabaseHelper.RunProcedureForPage(paraPage)
        Dim dt As DataTable = GetWorkTimeData.DBTable
        dt.Columns.Add("OLDTOTALDURATION")
        dt.Columns.Add("RealTotalDuration")
        For Each dr As DataRow In dt.Rows
            If para.Keys.Contains("SetUpTime") Then
                dr("RealTotalDuration") = Double.Parse(Double.Parse(Convert.ToDecimal(dr("totalduration")) + Convert.ToDecimal(dr("setuptimeduration"))).ToString("N3"))
            End If
            dr("OLDTOTALDURATION") = Double.Parse(Double.Parse(Convert.ToDecimal(dr("DURATIONPERUNIT")) * Convert.ToDecimal(dr("ACCEPTANCEQTY"))).ToString("N3"))
            If Not dr.IsNull("SPLITFULLNAME") Then '如果是单件单卡多人派工，员工替换为派工人员（对应的）信息
                dr("WORKER") = dr("SPLITFULLNAME")
                dr("WORKERCODE") = dr("SPLITEMPLOYEENAME")
            End If
        Next
        dt.AcceptChanges()
        GetWorkTimeData.DBTable = dt
        Dim total_dt As New DataTable
        If para.Keys.Contains("SetUpTime") Then
            total_dt = DatabaseHelper.Query_ReturnDatTable("SELECT SUM(A.TOTALDURATION+A.setuptimeduration) totalValue FROM(" + sb.ToString() + ") A")
        Else
            total_dt = DatabaseHelper.Query_ReturnDatTable("SELECT SUM(A.TOTALDURATION) totalValue FROM(" + sb.ToString() + ") A")
        End If
        If total_dt.Rows.Count > 0 Then
            If Not total_dt.Rows(0).IsNull("totalValue") Then
                totalStepTimeValue += Double.Parse(total_dt.Rows(0)("totalValue"))
            End If
        End If
    End Function

    ''' <summary>
    ''' 获取工时 
    ''' create:Wangjh 20220617 优化查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="totalStepTimeValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkTimeData2(ByVal para As Dictionary(Of String, String), ByRef totalStepTimeValue As Double) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetWorkTimeData2
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT CSTS.CONTAINERNAME,C.CONTAINERID,C.PRODUCTNAME,C.PRODUCTID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,NVL(CSTS.DURATIONPERUNIT,0) WORKDURATION,")
        sb.AppendLine("NVL(STD.DURATIONPERUNIT,0) STEPDURATION,CSTS.WORKFLOWSTEPNAME,CSTS.SCRAPQTY,(CSTS.QTY- CSTS.SCRAPQTY) ACCEPTANCEQTY,CSTS.VERIFICATIONDATE,CSTS.ADJUSTDATE")
        sb.AppendLine(<string>
        ,CASE
            WHEN (csts.verificationdate IS NULL AND csts.adjustdate IS NULL) THEN
            nvl(std.durationperunit, 0)
        ELSE
            nvl(csts.durationperunit, 0)
        END durationperunit,
        
        CASE
        WHEN csts.splitduration IS NOT NULL THEN
            nvl(csts.splitduration, -1)
        WHEN (csts.verificationdate IS NULL AND csts.adjustdate IS NULL) THEN
            round((csts.qty - csts.scrapqty) *(nvl(std.durationperunit, 0)) / nvl(csts.splitcount, 1),3)
        ELSE csts.totalduration
        END totalduration </string>.Value)
        sb.AppendLine(",CSTS.QTY,CSTS.WORKERID,T.TEAMNAME,WC.WORKCENTERNAME,S.SPECNO,CSTS.CREATEDATE,E.FULLNAME WORKER,E.EMPLOYEENAME WORKERCODE,EA.FULLNAME ADJUST,")
        'add WS.NOTES by tianFW 2018年5月31日-- add std.setuptime 2018年9月4日准备工时
        sb.AppendLine("EA.EMPLOYEENAME ADJUSTCODE,EV.FULLNAME VERIFICATION,EV.EMPLOYEENAME VERIFICATIONCODE,CSTS.SUBMITDATE,WS.NOTES,")
        'add tianfw 添加拆分人员的ES.FULLNAME SPLITWORKER,ES.EMPLOYEENAME SPLITEMPLOYEENAME 以及拆分数NVL(CSTS.SPLITCOUNT,1) SPLITCOUNT
        'remark by tianFW 2018年6月8日 NVL(CSTSD.SPLITDURATION,-1) 修改为-1，可能最后拆分时CSTSD.SPLITDURATION就是0
        sb.AppendLine("CSTS.CONTAINERSTEPTIMESHEETID,C.FAMILYLISTNAMES,ES.FULLNAME SPLITFULLNAME,ES.EMPLOYEENAME SPLITEMPLOYEENAME,NVL(CSTS.SPLITCOUNT,1) SPLITCOUNT,CSTS.splitduration,std.setuptime ")
        sb.AppendLine(<string>,(
SELECT SUM(NVL(csr.passqty,0)+NVL(csr.reworkqty,0)+NVL(csr.defectqty,0)) passqty FROM containersteprecord csr
WHERE  csr.containerid=csts.containerid AND csr.workflowstepid=csts.workflowstepid AND csr.usecompleted=1
GROUP BY csr.containerid,csr.workflowstepid
) completedQty </string>.Value) '批次，工序完工的总合格数
        sb.AppendLine("FROM ( ")

        sb.AppendFormat(<string>SELECT * FROM (
SELECT NULL splitduration,csts.workerid,csts.adjustemployeeid,csts.checkerid,csts.containercheckinfoid,csts.containerid, csts.containersteptimesheetid,csts.createdate,
  csts.durationperunit,csts.factoryid,csts.isuse,csts.qty,csts.scrapqty,csts.submitdate,csts.teamid,csts.totalduration,csts.workcenterid,csts.workflowstepid,
  csts.adjustdate,csts.containername,csts.verificationdate,csts.verificationemployeeid,csts.workflowstepname,csts.containerstepworkreportid,csts.stepinfoid,csts.state,csts.splitcount FROM CONTAINERSTEPTIMESHEET CSTS  
UNION ALL
SELECT cstsd.splitduration,CSTSD.workerid,csts.adjustemployeeid,csts.checkerid,csts.containercheckinfoid,csts.containerid, csts.containersteptimesheetid,csts.createdate,
  csts.durationperunit,csts.factoryid,csts.isuse,csts.qty,csts.scrapqty,csts.submitdate,csts.teamid,csts.totalduration,csts.workcenterid,csts.workflowstepid,
  csts.adjustdate,csts.containername,csts.verificationdate,csts.verificationemployeeid,csts.workflowstepname,csts.containerstepworkreportid,csts.stepinfoid,csts.state,csts.splitcount  FROM CONTAINERSTEPTIMESHEETSPLIT CSTSD
LEFT JOIN CONTAINERSTEPTIMESHEET CSTS  ON csts.containersteptimesheetid=CSTSD.containersteptimesheetid 
) csts  where csts.factoryid='{0}' AND (CSTS.QTY- CSTS.SCRAPQTY)>0 </string>.Value, para("FactoryID"))

        If para.Keys.Contains("WorkID") Then
            sb.AppendFormat(" AND CSTS.WORKERID='{0}'", para("WorkID"))
        Else
            If para.Keys.Contains("WorkCenterID") Then
                sb.AppendFormat(" AND CSTS.WORKCENTERID ='{0}'", para("WorkCenterID"))
            End If
            If para.Keys.Contains("TeamID") Then
                sb.AppendFormat(" AND NVL(csts.teamid,'{0}')", para("TeamID"))
            End If
        End If

        If para.Keys.Contains("Status") Then
            If para("Status") = "1" Then '未提交
                sb.AppendLine(" and NVL2(CSTS.SUBMITDATE,'1','0')='0' ")
            ElseIf para("Status") = "2" Then '已提交
                sb.AppendLine(" AND NVL2(CSTS.SUBMITDATE,'1','0')='1' AND NVL2(CSTS.ADJUSTDATE,'1','0')='0' AND NVL2(CSTS.VERIFICATIONDATE,'1','0')='0' ")
            ElseIf para("Status") = "3" Then '已核算
                sb.AppendLine(" AND NVL2(CSTS.ADJUSTDATE,'1','0')='1' ")
            ElseIf para("Status") = "4" Then '已核销
                sb.AppendLine(" AND NVL2(CSTS.VERIFICATIONDATE,'1','0')='1' ")
            End If
        End If

        If para.Keys.Contains("StartDate") Then
            sb.AppendFormat(" AND CSTS.SUBMITDATE >TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("StartDate")))
        End If
        If para.Keys.Contains("EndDate") Then
            sb.AppendFormat(" AND CSTS.SUBMITDATE-1 <TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("EndDate")))
        End If

        sb.AppendLine(" ) csts ")
        sb.AppendLine("INNER JOIN CONTAINERCURRENTINFO C ON C.CONTAINERID = CSTS.CONTAINERID  ") 'modify by tianFW 2018年2月5日将产品信息直接在CONTAINERCURRENTINFO表中获取
        sb.AppendLine("LEFT JOIN STEPSCHEDULINGDETAIL STD ON STD.STEPID = CSTS.WORKFLOWSTEPID  ")
        sb.AppendLine("LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = CSTS.WORKCENTERID  ")
        sb.AppendLine("LEFT JOIN TEAM T ON T.TEAMID = CSTS.TEAMID  ")
        sb.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSTS.WORKFLOWSTEPID")
        sb.AppendLine("LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID  ")
        sb.AppendLine("LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID) ")
        sb.AppendLine("LEFT JOIN EMPLOYEE E  ON E. EMPLOYEEID = CSTS.WORKERID ")
        sb.AppendLine("LEFT JOIN EMPLOYEE EA  ON EA. EMPLOYEEID = CSTS.ADJUSTEMPLOYEEID ")
        sb.AppendLine("LEFT JOIN EMPLOYEE EV  ON EV. EMPLOYEEID = CSTS.VERIFICATIONEMPLOYEEID ")
        sb.AppendLine("LEFT JOIN CONTAINERSTEPTIMESHEETSPLIT CSTSD  ON CSTSD.CONTAINERSTEPTIMESHEETID = CSTS.CONTAINERSTEPTIMESHEETID")
        sb.AppendLine("LEFT JOIN EMPLOYEE ES  ON ES. EMPLOYEEID =  NVL2( csts.splitduration,csts.workerid,NULL) ") '工时分配的工人
        sb.AppendLine(" WHERE 1=1 ")

        If para.Keys.Contains("ProductFamilyName") Then
            If para("ProductFamilyName") = "WP13汇总" Then
                sb.AppendLine(" AND (C.familylistnames LIKE '%WP13%' OR C.familylistnames LIKE '%全通%' OR C.familylistnames LIKE '%13通%'")
                sb.AppendLine(" OR C.familylistnames LIKE '%WP13AII%' OR C.familylistnames LIKE '%WPFI%' OR C.familylistnames LIKE '%WPFII%')")
            ElseIf para("ProductFamilyName") = "WP7汇总" Then
                sb.AppendLine(" AND (C.familylistnames LIKE '%WP7%' OR  C.familylistnames LIKE '%7通%')")
            Else
                sb.AppendFormat(" AND C.familylistnames LIKE '%{0}%'", para("ProductFamilyName"))
            End If
        ElseIf para.Keys.Contains("ProductFamily") Then
            sb.AppendFormat(" AND C.FAMILYLISTIDS LIKE '%{0}%'", para("ProductFamily"))
        End If

        If para.Keys.Contains("ProductName") Then
            sb.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If
        If para.Keys.Contains("StepNo") Then
            sb.AppendFormat(" AND S.SPECNO = '{0}'", para("StepNo"))
        End If
        If para.Keys.Contains("ContainerName") Then
            sb.AppendFormat(" AND C.CONTAINERNAME LIKE '{0}%'", para("ContainerName"))
        End If

        If para.Keys.Contains("ReportBdate") Or para.Keys.Contains("ReportEdate") Then '页面选择了报工开始或结束日期
            sb.AppendLine(<string>  AND EXISTS (
 SELECT 1 FROM ( 
SELECT cci.createdate checkdate,cci.containercheckinfoid FROM containercheckinfo cci
where cci.isuse=0
UNION ALL
SELECT cfci.createdate checkdate,cfci.containerfinishedcheckinfoid containercheckinfoid  FROM containerfinishedcheckinfo cfci
where cfci.isuse=0
) cci
WHERE cci.containercheckinfoid=csts.containercheckinfoid </string>.Value)
            If para.Keys.Contains("ReportBdate") Then
                If Not String.IsNullOrWhiteSpace(para("ReportBdate")) Then
                    sb.AppendFormat(" and cci.checkdate >= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportBdate")))
                End If
            End If

            If para.Keys.Contains("ReportEdate") Then
                If Not String.IsNullOrWhiteSpace(para("ReportEdate")) Then
                    sb.AppendFormat(" and cci.checkdate - 1 <= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportEdate")))
                End If
            End If
            sb.AppendLine(" ) ")
        End If

        sb.AppendLine("ORDER BY C.PRODUCTNAME,C.CONTAINERNAME,WS.SEQUENCE")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetWorkTimeData2 = DatabaseHelper.RunProcedureForPage(paraPage)
        Dim dt As DataTable = GetWorkTimeData2.DBTable
        dt.Columns.Add("OLDTOTALDURATION")
        dt.Columns.Add("RealTotalDuration")
        dt.Columns.Add("setuptimeduration") '燃烧专用
        For Each dr As DataRow In dt.Rows
            If para.Keys.Contains("SetUpTime") Then 'setuptimeduration赋值
                If String.IsNullOrWhiteSpace(dr("completedQty").ToString) Then
                    dr("setuptimeduration") = 0
                Else
                    If Not String.IsNullOrWhiteSpace(dr("splitduration").ToString) Then
                        dr("setuptimeduration") = (Val(dr("setuptime").ToString) / Val(dr("completedQty").ToString)) * Val(dr("ACCEPTANCEQTY").ToString)

                        dr("setuptimeduration") = dr("setuptimeduration") * Val(dr("splitduration").ToString) / (Val(dr("ACCEPTANCEQTY").ToString) * IIf(String.IsNullOrWhiteSpace(dr("WORKDURATION").ToString), dr("STEPDURATION").ToString, dr("WORKDURATION").ToString))

                        dr("setuptimeduration") = Math.Round(dr("setuptimeduration"), 3)
                    ElseIf String.IsNullOrWhiteSpace(dr("verificationdate").ToString) And String.IsNullOrWhiteSpace(dr("adjustdate").ToString) Then
                        dr("setuptimeduration") = (Val(dr("setuptime").ToString) / Val(dr("completedQty").ToString)) * Val(dr("ACCEPTANCEQTY").ToString)
                        dr("setuptimeduration") = dr("setuptimeduration") / Val(dr("splitcount").ToString)
                    Else '已核算或已核销

                    End If

                    If String.IsNullOrWhiteSpace(dr("setuptimeduration").ToString) Then
                        dr("setuptimeduration") = 0
                    End If

                End If

                dr("RealTotalDuration") = Double.Parse(Double.Parse(Convert.ToDecimal(dr("totalduration")) + Convert.ToDecimal(dr("setuptimeduration"))).ToString("N3"))

            End If
            dr("OLDTOTALDURATION") = Double.Parse(Double.Parse(Convert.ToDecimal(dr("DURATIONPERUNIT")) * Convert.ToDecimal(dr("ACCEPTANCEQTY"))).ToString("N3"))
            If Not dr.IsNull("splitduration") Then '如果是单件单卡多人派工，员工替换为派工人员（对应的）信息
                dr("WORKER") = dr("SPLITFULLNAME")
                dr("WORKERCODE") = dr("SPLITEMPLOYEENAME")
            End If
        Next
        dt.AcceptChanges()
        GetWorkTimeData2.DBTable = dt
        Dim total_dt As New DataTable
        If para.Keys.Contains("SetUpTime") Then
            total_dt = DatabaseHelper.Query_ReturnDatTable(String.Concat(<string>SELECT SUM(  CASE WHEN k.completedQty IS NULL THEN
              0
        WHEN k.splitduration IS NOT NULL THEN
        --准备工时/总合格数*合格数*(拆分工时/(合格数*单位工时))
        nvl(            
        round( 
        (k.setuptime / k.completedQty) * (k.ACCEPTANCEQTY) *              
        (             
        nvl(k.splitduration,0) / (k.ACCEPTANCEQTY * NVL(NVL(k.WORKDURATION,k.STEPDURATION),0))              
        )              
        ,3 )             
        ,0             
        )
        WHEN (k.verificationdate IS NULL AND k.adjustdate IS NULL) THEN              
        --准备工时/总合格数*合格数/拆分数(拆分数一般为1)
        nvl(round(             
        (k.setuptime / k.completedQty) *(k.ACCEPTANCEQTY) / nvl(k.splitcount, 1)                          
        , 3),0)             
        END ) +SUM(K.TOTALDURATION) totalValue  FROM  (</string>.Value, sb.ToString, " ) K"))

        Else
            total_dt = DatabaseHelper.Query_ReturnDatTable("SELECT SUM(A.TOTALDURATION) totalValue FROM(" + sb.ToString() + ") A")
        End If
        If total_dt.Rows.Count > 0 Then
            If Not total_dt.Rows(0).IsNull("totalValue") Then
                totalStepTimeValue += Double.Parse(total_dt.Rows(0)("totalValue"))
            End If
        End If

    End Function

    ''' <summary>
    ''' 提交工时
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SubmitStepTime(ByVal strID As String, ByVal strEmployeeID As String) As Boolean Implements uLMSStepTimeIBusiness.SubmitStepTime

        Dim strsql As String = String.Format("UPDATE CONTAINERSTEPTIMESHEET C SET SUBMITDATE=SYSDATE WHERE C.CONTAINERSTEPTIMESHEETID = '{0}'", strID)
        If (DatabaseHelper.ExecuteSql(strsql) > 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 核算工时
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AdjustStepTime(ByVal strID As String, ByVal strEmployeeID As String, ByVal perDuration As Decimal, ByVal totalDuration As Decimal) As Boolean Implements uLMSStepTimeIBusiness.AdjustStepTime

        Dim strsql As String = String.Format("UPDATE CONTAINERSTEPTIMESHEET C SET C.ADJUSTDATE=SYSDATE,C.ADJUSTEMPLOYEEID='{1}' " +
                                             ",C.DURATIONPERUNIT={2} ,C.TOTALDURATION={3}" +
                                             "WHERE C.CONTAINERSTEPTIMESHEETID = '{0}'", strID, strEmployeeID, perDuration, totalDuration)
        If (DatabaseHelper.ExecuteSql(strsql) > 0) Then
            Return True
        Else
            Return False
        End If
    End Function


    ''' <summary>
    ''' 核销工时
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function VerificationStepTime(ByVal strID As String, ByVal strEmployeeID As String, ByVal perDuration As Decimal, ByVal totalDuration As Decimal) As Boolean Implements uLMSStepTimeIBusiness.VerificationStepTime

        Dim strsql As String = String.Format("UPDATE CONTAINERSTEPTIMESHEET C SET VERIFICATIONDATE=SYSDATE,VERIFICATIONEMPLOYEEID='{1}' " +
                                                ",C.DURATIONPERUNIT={2} ,C.TOTALDURATION={3}" +
                                             "WHERE C.CONTAINERSTEPTIMESHEETID = '{0}'", strID, strEmployeeID, perDuration, totalDuration)
        If (DatabaseHelper.ExecuteSql(strsql) > 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取ProductfamilyName create tianfW
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetProductfamilyName() As DataTable Implements IBusiness.uLMSStepTimeIBusiness.GetProductfamilyName
        Dim strsql As String = "SELECT P.PRODUCTFAMILYNAME, P.PRODUCTFAMILYID FROM PRODUCTFAMILY P order by p.PRODUCTFAMILYNAME"
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function

    ''' <summary>
    ''' 删除分配工时记录 create tianFW 2017/09/22
    ''' </summary>
    ''' <param name="SteptimeID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelSteptimeSplitInfoByID(ByVal SteptimeID As String) As Boolean Implements uLMSStepTimeIBusiness.DelSteptimeSplitInfoByID
        Dim strsql As String = String.Format("DELETE FROM CONTAINERSTEPTIMESHEETSPLIT CS_ WHERE CS_.CONTAINERSTEPTIMESHEETID='{0}'", SteptimeID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 根据id获取分配工时信息 create tianFW  2017/09/22
    ''' </summary>
    ''' <param name="SteptimeID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSteptimeSplitInfoByID(ByVal SteptimeID As String) As DataTable Implements uLMSStepTimeIBusiness.GetSteptimeSplitInfoByID
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CS_.*,EM.FULLNAME SPLITEMPLOYEENAME,EM1.FULLNAME WORKERNAME FROM CONTAINERSTEPTIMESHEETSPLIT CS_ ")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CS_.SPLITEMPLOYEEID")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM1 ON EM1.EMPLOYEEID = CS_.WORKERID")
        strsql.AppendFormat("WHERE CS_.CONTAINERSTEPTIMESHEETID='{0}' ORDER BY CS_.WORKERID", SteptimeID)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function


    ''' <summary>
    ''' 添加分配工时信息 create tianFW 2017/09/22
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddSteptimeSplitInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSStepTimeIBusiness.AddSteptimeSplitInfo
        Dim strsql As String =
            <string>
                INSERT INTO CONTAINERSTEPTIMESHEETSPLIT(CONTAINERSTEPTIMESHEETID,CREATEDATE,SPLITDURATION,SPLITEMPLOYEEID,WORKERID)
                VALUES(:CONTAINERSTEPTIMESHEETID,SYSDATE,:SPLITDURATION,:SPLITEMPLOYEEID,:WORKERID)
            </string>
        Dim oraclepara(3) As OracleParameter
        oraclepara(0) = DatabaseHelper.NewParameter(":CONTAINERSTEPTIMESHEETID", para("CONTAINERSTEPTIMESHEETID"), OracleType.VarChar)
        oraclepara(1) = DatabaseHelper.NewParameter(":SPLITDURATION", Double.Parse(para("SPLITDURATION")), OracleType.Number)
        oraclepara(2) = DatabaseHelper.NewParameter(":SPLITEMPLOYEEID", para("SPLITEMPLOYEEID"), OracleType.VarChar)
        oraclepara(3) = DatabaseHelper.NewParameter(":WORKERID", para("WORKERID"), OracleType.VarChar)
        If DatabaseHelper.ExecuteSql(strsql, oraclepara) Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取没有维护完工序工时的工艺信息 create tianFW 2018年5月7日
    ''' 添加工艺描述 Modify by Yuxj20180718
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetDoNotMaintainAllStepDurationUnitWorkflowInfo(ByVal factoryid As String, ByVal m_PageSize As Integer, ByVal index As Integer) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetDoNotMaintainAllStepDurationUnitWorkflowInfo
        Dim strsql As New StringBuilder
        Dim sql As String =
            <string>
                SELECT pb.productname || ':' || p.productrevision || '(' ||pt.producttypename || ')' productinfo,wf.workflowid,
                wfb.workflowname || ':' || wf.workflowrevision || '(' ||wf.workflowtype || ')' workflowname,wfb.workflowbaseid,p.productid,
                pb.revofrcdid pbrevofrcdid,wf.description wfdesc
                FROM product p
                LEFT JOIN productbase pb ON p.productbaseid = pb.productbaseid
                LEFT JOIN workflowbase wfb1 ON wfb1.workflowbaseid = p.workflowbaseid
                LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid
                LEFT JOIN workflow wf1 ON wf1.workflowid = nvl(wfb1.revofrcdid, p.workflowid)
                LEFT JOIN workflowbase wfb ON wfb.workflowbaseid = wf1.workflowbaseid
                LEFT JOIN workflow wf ON wf.workflowbaseid = wfb.workflowbaseid
            </string>
        strsql.AppendLine(sql)
        strsql.AppendLine("INNER JOIN (SELECT ws.workflowid FROM workflowstep ws")
        strsql.AppendLine("LEFT JOIN workflow wf ON wf.workflowid = ws.workflowid")
        strsql.AppendLine("LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid")
        strsql.AppendLine(<string> left join specbase sb on sb.specbaseid=ws.specbaseid
left join spec s on s.specid=nvl(sb.revofrcdid,ws.specid) </string>.Value)
        strsql.AppendLine("LEFT JOIN (SELECT sif.workflowstepid,sif.stepinfoid,sif.durationperunit FROM stepinfo sif  ")
        strsql.AppendLine(" INNER JOIN stepinfobase sib ON sib.revofrcdid=sif.stepinfoid ) SIO on sio.workflowstepid=ws.workflowstepid ")

        'Modify by Yuxj20190416增加条件sio.stepinfoid is  NULL
        strsql.AppendLine("WHERE (( sio.stepinfoid is  NULL and sd.stepid IS NOT NULL AND sd.durationperunit IS NULL) or (sio.stepinfoid is not NULL AND sio.durationperunit IS NULL))")
        strsql.AppendFormat(" AND wf.factoryid='{0}' and s.factoryid='{0}' AND ws.sequence IS NOT NULL ", factoryid)
        strsql.AppendLine("GROUP BY ws.workflowid) WFID ON WFID.workflowid = wf.workflowid")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = index
        paraPage.PageSize = m_PageSize
        paraPage.strSQL = strsql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' Modify by Yuxj20190415
    ''' 获取装试没有维护工时的工艺
    ''' </summary>
    ''' <param name="factoryid"></param>
    ''' <param name="m_PageSize"></param>
    ''' <param name="index"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetDoNotMaintainAllStepDurationUnit(ByVal factoryid As String, ByVal m_PageSize As Integer, ByVal index As Integer,
                                                 ByVal BeginDate As String, ByVal EndDate As String) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetDoNotMaintainAllStepDurationUnit
        Dim strsql As New StringBuilder
        Dim sql As String = String.Empty
        sql =
       <string>
                SELECT decode(pb.productname,null,null,pb.productname || ':' || p.productrevision || '(' ||pt.producttypename || ')') productinfo,
                wf.workflowid,
                wfb.workflowname || ':' || wf.workflowrevision || '(' ||wf.workflowtype || ')' workflowname,
                wfb.workflowbaseid,p.productid,
                pb.revofrcdid pbrevofrcdid,wf.description wfdesc,wf.process_no FROM workflow wf
                LEFT JOIN Changestatus ct ON ct.parentid = wf.workflowid --yuxj20200430 按工艺变更时间降序排序
                LEFT JOIN workflowbase wfb ON wfb.workflowbaseid=wf.workflowbaseid
                LEFT JOIN enginepartplan ep ON ep.workflowid=wf.workflowid
                LEFT JOIN product p ON ep.productid=p.productid
                LEFT JOIN producttype pt ON pt.producttypeid = p.producttypeid
                LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid                
                INNER JOIN (
                SELECT wf.workflowid FROM workflow wf 
                INNER join workflowstep wfs ON wf.workflowid=wfs.workflowid
                LEFT JOIN stepschedulingdetail st ON st.stepid=wfs.workflowstepid
                LEFT JOIN stepinfo sio ON wfs.workflowstepid=sio.Workflowstepid               
                WHERE wf.workflowtype='施工工艺' and
                 (( sio.stepinfoid is  NULL AND st.stepid IS NOT NULL AND st.durationperunit IS NULL) 
                 or (sio.stepinfoid is not NULL AND sio.durationperunit IS NULL))
                 GROUP BY wf.workflowid
                 ) wff ON wff.workflowid=wf.workflowid
            </string>
        sql += "  WHERE wf.workflowtype='施工工艺' "
        If BeginDate <> String.Empty Then
            sql += String.Format(" and to_date(wf.process_no,'yyyy/mm/dd  hh24:mi:ss')>=to_date('{0}','yyyy/mm/dd  hh24:mi:ss') ", BeginDate)
            If EndDate <> String.Empty Then
                sql += String.Format(" and to_date(wf.process_no,'yyyy/mm/dd  hh24:mi:ss')<=to_date('{0}','yyyy/mm/dd  hh24:mi:ss') ", EndDate)
            End If
        End If
        sql += " order by ct.lastchangedate DESC "
        strsql.AppendLine(sql)
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = index
        paraPage.PageSize = m_PageSize
        paraPage.strSQL = strsql.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取没有件号关联的工艺信息 create by tianFW 2018年6月12日
    ''' </summary>
    ''' <param name="workflowinfo"></param>
    ''' <returns></returns>
    ''' <remarks>add YangJiang 20180727 FactoryID</remarks>
    Public Function GetWorkflowOfNoRelevantToProduct(ByVal workflowinfo As String, ByVal FactoryID As String) As DataTable Implements uLMSStepTimeIBusiness.GetWorkflowOfNoRelevantToProduct
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT wfb.workflowname || ':' || wf.workflowrevision || '(' ||wf.workflowtype || ')' workflowname,wf.workflowid,wf.description")
        strsql.AppendLine("FROM workflow wf")
        strsql.AppendLine("LEFT JOIN Workflowbase wfb ON wfb.workflowbaseid = wf.workflowbaseid")
        strsql.AppendFormat(" WHERE wf.factoryid='{0}' and (Upper(wf.workflowtype) LIKE Upper('%{1}%') OR Upper(wf.description) LIKE Upper('%{1}%') OR Upper(wfb.workflowname) LIKE Upper('%{1}%'))", FactoryID, workflowinfo)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 修改工艺工艺员信息 create by tianFW 2018年6月21日
    ''' </summary>
    ''' <param name="WFID">工艺ID</param>
    ''' <param name="ZMID">工艺默认工艺员ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ChangeWFCheckTechnique(ByVal WFID As String, ByVal ZMID As String) As Boolean Implements uLMSStepTimeIBusiness.ChangeWFCheckTechnique
        Dim strsql As String = String.Format("UPDATE workflow wf SET wf.zgemployeeid='{0}' WHERE wf.workflowid='{1}'", ZMID, WFID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' Add by Yuxj20200401 试车工时
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="totalStepTimeValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetTDMWorkTimeData(ByVal para As Dictionary(Of String, String), ByRef totalStepTimeValue As Double) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetTDMWorkTimeData
        Dim strsql As String = <string>
                                SELECT TDM.STEPTIMESID,
                                       TDM.MFGORDERNAME,
                                       TDM.ENGINENO,
                                       TDM.PRODUCTFAMILYNAME,
                                       TDM.TASKTYPE,
                                       TDM.SPECNO,
                                       TDM.SPECNAME,
                                       TDM.FULLNAME,
                                       TDM.EMPLOYEENAME,
                                       TDM.COMPLETEDATE,
                                       TDM.DURATION,                                       
                                       TDM.DURATIONQTY TotalDuraion,
                                       TDM.STATUS
                                  FROM TDM_STEPTIMESHEET TDM
                                LEFT JOIN employee e ON e.employeename=tdm.employeename
                                LEFT JOIN Sessionvalues sv ON sv.sessionvaluesid=e.sessionvaluesid
                                LEFT JOIN WORKCENTER WC ON wc.workcenterid=sv.workcenterid --非发动机事业部由组到工区
                                LEFT JOIN TEAMEMPLOYEE TE ON TE.EMPLOYEEID=E.EMPLOYEEID
                                LEFT JOIN TEAM T ON T.TEAMID=TE.TEAMID
                                LEFT JOIN SHIFT SS ON SS.SHIFTID=T.SHIFTID
                                LEFT JOIN WORKCENTER WCC ON WCC.WORKCENTERID=SS.WORKCENTERID --发动机事业部 由班关联到工区
                                where 1=1
                               </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("FullName") Then
            sb.AppendFormat(" and tdm.FullName = '{0}'", para("FullName"))
        End If

        If para.ContainsKey("WorkID") Then
            sb.AppendFormat(" and e.employeeid = '{0}'", para("WorkID"))
        End If

        If para.ContainsKey("WorkCenterID") Then
            sb.AppendFormat(" and nvl(WCC.WorkCenterID, wc.WorkCenterID) = '{0}'", para("WorkCenterID"))
        End If

        If para.Keys.Contains("ShiftID") Then
            sb.AppendFormat(" AND {0}", para("ShiftID"))
        End If

        If para.Keys.Contains("TeamID") Then
            sb.AppendFormat(" AND {0}", para("TeamID"))
        End If

        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat(" and tdm.engineno like '%{0}%'", para("MachineStart"))
        End If

        If para.Keys.Contains("ReportBdate") Then
            sb.AppendFormat(" and tdm.completedate >= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportBdate")))
            If para.Keys.Contains("ReportEdate") Then
                sb.AppendFormat(" and tdm.completedate <= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportEdate")))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetTDMWorkTimeData = DatabaseHelper.RunProcedureForPage(paraPage)

        Dim strTotalSql = "select nvl(sum(nvl(TotalDuraion,0)),0) TotalDuration from (" + sb.ToString + ") "
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strTotalSql)
        If dt.Rows.Count > 0 Then
            totalStepTimeValue = dt.Rows(0)("TotalDuration").ToString
        Else
            totalStepTimeValue = 0
        End If

    End Function

    ''' <summary>
    ''' 获取工时(装试)
    ''' add YangJiang 20180616
    ''' Modify Yuxj20181101 装试分厂checktype ='1'初装，checktype ='2'检装
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks>add and csw.isuse = 0 by tianFW 2018年9月29日</remarks>
    Function GetStepWorkTimeData(ByVal para As Dictionary(Of String, String), ByRef totalStepTimeValue As Double) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetStepWorkTimeData
        Dim sb As New StringBuilder
        'If para.Keys.Contains("ReportBdate") Or para.Keys.Contains("ReportEdate") Then '将根据报工时间查询修改为根据检验时间查询
        '    sb.AppendLine("SELECT * FROM (")
        'End If
        Dim str As String =
            <string>
                SELECT CSTS.CONTAINERNAME,C.CONTAINERID,C.PRODUCTNAME,C.PRODUCTID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,NVL(CSTS.DURATIONPERUNIT,0) WORKDURATION,
                NVL(STD.DURATIONPERUNIT,0) STEPDURATION,CSTS.WORKFLOWSTEPNAME,CSTS.SCRAPQTY,(CSTS.QTY- CSTS.SCRAPQTY) ACCEPTANCEQTY,CSTS.VERIFICATIONDATE,CSTS.ADJUSTDATE,
                 CASE
                  WHEN(csts.stepinfoid IS NOT NULL AND csw.checktype ='1' ) THEN
                coalesce(CSTSD.SPLITDURATION,csts.totalduration,round(SI.DURATIONPERUNIT / NVL(CSTS.SPLITCOUNT, 1), 2), 0)                
                  WHEN(csts.stepinfoid IS NOT NULL AND csw.checktype ='2') THEN
                coalesce(CSTSD.SPLITDURATION,csts.totalduration, round(SI.Durationperunit2 / NVL(CSTS.SPLITCOUNT, 1), 2), 0)
                  ELSE
                coalesce(csts.totalduration, csts.durationperunit, cstsd.splitduration, round(STD.DURATIONPERUNIT / NVL(CSTS.SPLITCOUNT, 1), 2), 0)
                END totalduration,               
                CSTS.QTY,CSTSD.WORKERID,T.TEAMNAME,WC.WORKCENTERNAME,WC.WORKCENTERID,CSTS.CREATEDATE,DECODE(es.employeeid,NULL,E.FULLNAME,ES.FULLNAME) WORKER,E.EMPLOYEENAME WORKERCODE,EA.FULLNAME ADJUST,
                EA.EMPLOYEENAME ADJUSTCODE,EV.FULLNAME VERIFICATION,EV.EMPLOYEENAME VERIFICATIONCODE,CSTS.SUBMITDATE,WS.NOTES,
                CSTS.CONTAINERSTEPTIMESHEETID,C.FAMILYLISTNAMES,ES.FULLNAME SPLITFULLNAME,ES.EMPLOYEENAME SPLITEMPLOYEENAME,NVL(CSTS.SPLITCOUNT,1) SPLITCOUNT
                ,ss.Shiftname,si.description stepinfoname,ua.attributevalue,epl.containerno,DECODE(csw.checktype,'1','初装','2','检装') checktype
                ,CFI.CREATEDATE CHECKDATE,p.description
                FROM CONTAINERSTEPTIMESHEET CSTS
                INNER JOIN CONTAINERCURRENTINFO C ON C.CONTAINERID = CSTS.CONTAINERID
                LEFT JOIN CONTAINERCHECKINFO CFI ON CFI.CONTAINERCHECKINFOID = CSTS.CONTAINERCHECKINFOID              
                LEFT JOIN STEPSCHEDULINGDETAIL STD ON STD.STEPID = CSTS.WORKFLOWSTEPID
                LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = CSTS.WORKCENTERID
                LEFT JOIN TEAM T ON T.TEAMID = CSTS.TEAMID
                LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSTS.WORKFLOWSTEPID               
                LEFT JOIN EMPLOYEE E  ON E. EMPLOYEEID = CSTS.WORKERID
                LEFT JOIN EMPLOYEE EA  ON EA. EMPLOYEEID = CSTS.ADJUSTEMPLOYEEID
                LEFT JOIN EMPLOYEE EV  ON EV. EMPLOYEEID = CSTS.VERIFICATIONEMPLOYEEID
                LEFT JOIN CONTAINERSTEPTIMESHEETSPLIT CSTSD  ON CSTSD.CONTAINERSTEPTIMESHEETID = CSTS.CONTAINERSTEPTIMESHEETID
                LEFT JOIN EMPLOYEE ES  ON ES. EMPLOYEEID =  CSTSD.WORKERID
                LEFT JOIN shift SS ON ss.shiftid=t.shiftid
                LEFT JOIN stepinfo SI ON si.Stepinfoid=csts.stepinfoid
                LEFT JOIN stepinfobase sib ON sib.stepinfobaseid=si.Stepinfobaseid
                LEFT JOIN userattribute ua ON ua.parentid=c.mfgorderid 
                LEFT JOIN enginepartplan EPL ON  NVL(EPL.enginecontainerid, EPL.containerid)=c.containerid
                LEFT JOIN product p ON p.productid=EPL.productid
                LEFT JOIN containerstepworkreport csw ON cfi.containerstepworkreportid=csw.containerstepworkreportid
            </string>
        sb.AppendLine(str)
        '增加条件AND ua.userattributename='发动机' and csw.isuse = 0 
        sb.AppendFormat(" WHERE (CSTS.QTY- CSTS.SCRAPQTY)>0 AND CSTS.FACTORYID='{0}' AND ua.userattributename='发动机'  ", para("FactoryID"))
        'Modify by Yuxj20190422装试分厂，将集件去封放在批产工时中
        sb.AppendLine(" and ((csw.isuse = 0) OR  INSTR(si.Description,'集件')>0 or INSTR(si.Description,'去封')>0  )  ")
        If para.ContainsKey("MachineStart") Then
            'Modify by Yuxj20180928 发动机查询更改为模糊查询
            sb.AppendFormat("AND ua.attributevalue like '%{0}%'", para("MachineStart"))
        End If
        If para.ContainsKey("ShiftID") Then
            sb.AppendFormat("AND {0}", para("ShiftID"))
        End If
        If para.ContainsKey("MachineStatus") Then
            sb.AppendFormat("AND EPL.workflowstepid='{0}'", para("MachineStatus")) 'si.Stepinfoid==>ep1.workflowstepid
        End If
        If para.ContainsKey("CheckType") Then
            'sb.AppendFormat("AND csw.checktype='{0}'", para("CheckType"))
            If para("CheckType") = "1" Then
                sb.AppendLine(" and EPL.workflowstepid is not null ") '批产任务工时
            ElseIf para("CheckType") = "2" Then
                sb.AppendLine(" and EPL.workflowstepid is null ") '施工单工时
            End If
        End If
        If para.Keys.Contains("WorkID") Then 'modify tianFW 将只从工时表查询人员，添加拆分表查询人员
            sb.AppendFormat(" AND NVL(CSTSD.WORKERID,CSTS.WORKERID)='{0}'", para("WorkID"))
        Else
            If para.Keys.Contains("WorkCenterID") Then
                sb.AppendFormat(" AND WC.WORKCENTERID ='{0}'", para("WorkCenterID"))
            End If
            'add YangJiang 20180717
            If para.Keys.Contains("ShiftID") Then
                sb.AppendFormat(" AND {0}", para("ShiftID"))
            End If

            If para.Keys.Contains("TeamID") Then
                sb.AppendFormat(" AND {0}", para("TeamID"))
            End If
        End If
        If para.Keys.Contains("Status") Then
            If para("Status") = "1" Then '未提交
                sb.AppendLine(" AND CSTS.ADJUSTDATE IS NULL and CSTS.VERIFICATIONDATE IS NULL ")
            ElseIf para("Status") = "2" Then '已提交
                sb.AppendLine(" AND CSTS.SUBMITDATE IS NOT NULL AND CSTS.ADJUSTDATE IS NULL AND CSTS.VERIFICATIONDATE IS NULL")
            ElseIf para("Status") = "3" Then '已核算
                sb.AppendLine(" AND CSTS.ADJUSTDATE IS NOT NULL ")
            ElseIf para("Status") = "4" Then '已核销
                sb.AppendLine(" AND CSTS.VERIFICATIONDATE IS NOT NULL ")
            End If
        End If
        If para.Keys.Contains("ProductName") Then
            sb.AppendFormat(" AND C.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If


        'If para.Keys.Contains("ReportBdate") Or para.Keys.Contains("ReportEdate") Then
        '    sb.AppendLine(") K WHERE")
        'End If
        If para.Keys.Contains("ReportBdate") Then
            sb.AppendFormat(" and NVL(CFI.Createdate,CSTS.CREATEDATE) >= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportBdate")))
        End If
        If para.Keys.Contains("ReportEdate") Then
            sb.AppendFormat(" and NVL(CFI.Createdate,CSTS.CREATEDATE) <= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportEdate")))
        End If
        sb.AppendLine("ORDER BY C.PRODUCTNAME,C.CONTAINERNAME,WS.SEQUENCE")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetStepWorkTimeData = DatabaseHelper.RunProcedureForPage(paraPage)

        Dim strTotalSql = "select nvl(sum(nvl(TotalDuration,0)),0) TotalDuration from (" + sb.ToString + ") "
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strTotalSql)
        If dt.Rows.Count > 0 Then
            totalStepTimeValue = dt.Rows(0)("TotalDuration").ToString
        Else
            totalStepTimeValue = 0
        End If

    End Function

    ''' <summary>
    ''' Add by Yuxj20181015
    ''' 增加油封工时显示
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="totalStepTimeValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetOilSealWorkTimeData(ByVal para As Dictionary(Of String, String), ByRef totalStepTimeValue As Double) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetOilSealWorkTimeData
        Dim strsql As String =
            <string>
                SELECT ew.employeeid WorkerId,es.enginepartoilsealid containercheckinfoid,csts.ContainerStepTimeSheetID,es.createdate,es.engineno attributevalue,es.containerno,'油封' WORKFLOWSTEPNAME,pb.productname stepinfoname,
                        wc.workcentername,WC.WORKCENTERID,ss.Shiftname,t.teamname,e.fullname WORKER,pc.description,'油封' checktype, nvl(trunc(os.oilsealdurationperunit/NVL(ec.WorkerCount,1),2),0) totalduration,
                        csts.SubmitDate, EA.FULLNAME ADJUST,EA.EMPLOYEENAME ADJUSTCODE,EV.FULLNAME VERIFICATION,EV.EMPLOYEENAME VERIFICATIONCODE,
                        CSTS.ADJUSTDATE,CSTS.VERIFICATIONDATE
                        FROM enginepartoilseal es 
                        LEFT JOIN CONTAINERSTEPTIMESHEET CSTS ON csts.containercheckinfoid=es.enginepartoilsealid
                        LEFT JOIN EMPLOYEE EA  ON EA. EMPLOYEEID = CSTS.ADJUSTEMPLOYEEID
                        LEFT JOIN EMPLOYEE EV  ON EV. EMPLOYEEID = CSTS.VERIFICATIONEMPLOYEEID
                        LEFT JOIN product p ON es.productid=p.productid
                        LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid
                        LEFT JOIN product pc ON es.partid=pc.productid
                        LEFT JOIN oilsealcycleinfo os ON es.oilsealcycleinfoid=os.oilsealcycleinfoid
                        INNER JOIN enginepartoilsealworker ew ON es.enginepartoilsealid=ew.enginepartoilsealid
                        LEFT JOIN 
                        (SELECT ew.enginepartoilsealid,COUNT(1) WorkerCount  
                        FROM enginepartoilsealworker ew GROUP BY ew.enginepartoilsealid) ec ON ec.enginepartoilsealid=es.enginepartoilsealid
                        LEFT JOIN EMPLOYEE E  ON E. EMPLOYEEID =  ew.employeeid
                        LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid
                        LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid
                        LEFT JOIN shift SS ON ss.shiftid=e.shiftid
                        LEFT JOIN teamemployee te ON te.employeeid=e.employeeid
                        LEFT JOIN team t ON t.teamid=te.teamid
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("MachineStart") Then
            'Modify by Yuxj20180928 发动机查询更改为模糊查询
            sb.AppendFormat("AND es.engineno like '%{0}%'", para("MachineStart"))
        End If
        If para.ContainsKey("ShiftID") Then
            sb.AppendFormat("AND {0}", para("ShiftID"))
        End If

        If para.Keys.Contains("WorkID") Then 'modify tianFW 将只从工时表查询人员，添加拆分表查询人员
            sb.AppendFormat(" AND ew.employeeid='{0}'", para("WorkID"))
        Else
            If para.Keys.Contains("WorkCenterID") Then
                sb.AppendFormat(" AND WC.WORKCENTERID ='{0}'", para("WorkCenterID"))
            End If
            'add YangJiang 20180717
            If para.Keys.Contains("ShiftID") Then
                sb.AppendFormat(" AND {0}", para("ShiftID"))
            End If

            If para.Keys.Contains("TeamID") Then
                sb.AppendFormat(" AND {0}", para("TeamID"))
            End If
        End If
        If para.Keys.Contains("Status") Then
            If para("Status") = "1" Then '未提交
                '实际业务中取消了提交操作，未提交改为为处理
                'sb.AppendLine(" AND CSTS.SUBMITDATE IS NULL ")
                sb.AppendLine(" AND CSTS.ADJUSTDATE IS NULL and CSTS.VERIFICATIONDATE IS NULL ")
            ElseIf para("Status") = "2" Then '已提交
                sb.AppendLine(" AND CSTS.SUBMITDATE IS NOT NULL AND CSTS.ADJUSTDATE IS NULL AND CSTS.VERIFICATIONDATE IS NULL")
            ElseIf para("Status") = "3" Then '已核算
                sb.AppendLine(" AND CSTS.ADJUSTDATE IS NOT NULL ")
            ElseIf para("Status") = "4" Then '已核销
                sb.AppendLine(" AND CSTS.VERIFICATIONDATE IS NOT NULL ")
            End If
        End If
        If para.Keys.Contains("ProductName") Then
            sb.AppendFormat(" AND pb.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        End If

        If para.Keys.Contains("ReportBdate") Then
            sb.AppendFormat(" and es.CREATEDATE>= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportBdate")))
            If para.Keys.Contains("ReportEdate") Then
                sb.AppendFormat(" and es.CREATEDATE<= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportEdate")))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetOilSealWorkTimeData = DatabaseHelper.RunProcedureForPage(paraPage)

        Dim strTotalSql = "select nvl(sum(nvl(TotalDuration,0)),0) TotalDuration from (" + sb.ToString + ") "
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strTotalSql)
        If dt.Rows.Count > 0 Then
            totalStepTimeValue = dt.Rows(0)("TotalDuration").ToString
        Else
            totalStepTimeValue = 0
        End If

    End Function

    ''' <summary>
    ''' Add by Yuxj20181017
    ''' 获取集件/去封工时
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="totalStepTimeValue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCollectWorkTimeData(ByVal para As Dictionary(Of String, String), ByRef totalStepTimeValue As Double) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetCollectWorkTimeData
        Dim strsql As String =
            <string>
                SELECT  
                  csts.containername,csts.containerid,csts.createdate,u.attributevalue,sib.stepinfoname,si.Stepinfoid,'集件去封' checktype,e.fullname WORKER,
                    csts.workflowstepname,csts.workflowstepid,CSTS.SCRAPQTY,(CSTS.QTY- CSTS.SCRAPQTY) ACCEPTANCEQTY,EA.FULLNAME ADJUST,
                  CSTS.VERIFICATIONDATE,CSTS.ADJUSTDATE,csts.TotalDuration, CSTS.CONTAINERSTEPTIMESHEETID,ss.shiftname,                              
                  CSTS.QTY,CSTS.WORKERID,T.TEAMNAME,WC.WORKCENTERNAME,EA.EMPLOYEENAME ADJUSTCODE,EV.FULLNAME VERIFICATION,EV.EMPLOYEENAME VERIFICATIONCODE,CSTS.SUBMITDATE   
                    FROM CONTAINERSTEPTIMESHEET CSTS
                    LEFT JOIN container c ON csts.containerid=c.containerid
                    LEFT JOIN mfgorder m ON c.mfgorderid=m.mfgorderid
                    LEFT JOIN userattribute u ON u.parentid=m.mfgorderid
                    LEFT JOIN stepinfo SI ON si.Stepinfoid=csts.stepinfoid
                    LEFT JOIN stepinfobase sib ON si.Stepinfobaseid=sib.stepinfobaseid
                    LEFT JOIN EMPLOYEE E  ON E. EMPLOYEEID = CSTS.WORKERID
                    LEFT JOIN EMPLOYEE EA  ON EA. EMPLOYEEID = CSTS.ADJUSTEMPLOYEEID
                    LEFT JOIN EMPLOYEE EV  ON EV. EMPLOYEEID = CSTS.VERIFICATIONEMPLOYEEID
                    LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = CSTS.WORKCENTERID
                    LEFT JOIN TEAM T ON T.TEAMID = CSTS.TEAMID
                    LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSTS.WORKFLOWSTEPID
                    LEFT JOIN shift SS ON ss.shiftid=t.shiftid
                    WHERE ws.workflowstepname LIKE '%集件%'
                        and u.userattributename='发动机'
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat("AND u.attributevalue like '%{0}%'", para("MachineStart"))
        End If
        If para.ContainsKey("ShiftID") Then
            sb.AppendFormat("AND {0}", para("ShiftID"))
        End If

        If para.Keys.Contains("WorkID") Then 'modify tianFW 将只从工时表查询人员，添加拆分表查询人员
            sb.AppendFormat(" AND csts.employeeid='{0}'", para("WorkID"))
        Else
            If para.Keys.Contains("WorkCenterID") Then
                sb.AppendFormat(" AND WC.WORKCENTERID ='{0}'", para("WorkCenterID"))
            End If
            'add YangJiang 20180717
            If para.Keys.Contains("ShiftID") Then
                sb.AppendFormat(" AND {0}", para("ShiftID"))
            End If

            If para.Keys.Contains("TeamID") Then
                sb.AppendFormat(" AND {0}", para("TeamID"))
            End If
        End If
        If para.Keys.Contains("Status") Then
            If para("Status") = "1" Then '未提交
                sb.AppendLine(" AND CSTS.ADJUSTDATE IS NULL and CSTS.VERIFICATIONDATE IS NULL ")
            ElseIf para("Status") = "2" Then '已提交
                sb.AppendLine(" AND CSTS.SUBMITDATE IS NOT NULL AND CSTS.ADJUSTDATE IS NULL AND CSTS.VERIFICATIONDATE IS NULL")
            ElseIf para("Status") = "3" Then '已核算
                sb.AppendLine(" AND CSTS.ADJUSTDATE IS NOT NULL ")
            ElseIf para("Status") = "4" Then '已核销
                sb.AppendLine(" AND CSTS.VERIFICATIONDATE IS NOT NULL ")
            End If
        End If
        'If para.Keys.Contains("ProductName") Then
        '    sb.AppendFormat(" AND pb.PRODUCTNAME LIKE'%{0}%'", para("ProductName"))
        'End If

        If para.Keys.Contains("ReportBdate") Then
            sb.AppendFormat(" csts.CREATEDATE>= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportBdate")))
            If para.Keys.Contains("ReportEdate") Then
                sb.AppendFormat(" csts.CREATEDATE<= TO_DATE('{0}', 'YYYY-MM-DD HH24:MI:SS')", DateTime.Parse(para("ReportEdate")))
            End If
        End If

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetCollectWorkTimeData = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取工序派工没有报工的信息表 create by tianFW 2018年7月4日
    ''' </summary>
    ''' <param name="stepid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function VerifyStepDispatchInfoOfNotWorkReport(ByVal stepid As String) As DataTable Implements uLMSStepTimeIBusiness.VerifyStepDispatchInfoOfNotWorkReport
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT csd.workflowstepid FROM containerstepdispatch csd")
        strsql.AppendLine("LEFT JOIN containerstepworkreport csw ON csw.containerstepdispatchid  = csd.containerstepdispatchid")
        strsql.AppendFormat(" WHERE csw.containerstepworkreportid IS NULL AND csd.workflowstepid='{0}' AND csw.isuse =0 AND csd.isuse=0 ", stepid)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' Add by Yuxj20181016
    ''' 将油封信息插入工时表
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function InsertOiltoSheet(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSStepTimeIBusiness.InsertOiltoSheet
        InsertOiltoSheet = False
        Dim sb As New StringBuilder
        sb.AppendFormat(" select * from CONTAINERSTEPTIMESHEET cs where cs.workerid='{0}' and cs.ContainerCheckinfoId='{1}'", para("WorkerId"), para("ContainerCheckinfoId"))
        If DatabaseHelper.Query_ReturnDatTable(sb.ToString).Rows.Count > 0 Then
            '已经插入
            InsertOiltoSheet = True
            Exit Function
        End If

        sb = New StringBuilder
        sb.AppendLine(" INSERT INTO CONTAINERSTEPTIMESHEET")
        sb.AppendLine(" (createdate,workcenterid,workerid,workflowstepname,containersteptimesheetid,containercheckinfoid, totalduration) ")
        sb.AppendLine(" values ")
        sb.AppendFormat("sysdate,'{0}','{1}','{2}','{3}','{4}','{5}'", para("WorkCenterId"), para("WorkerId"), "油封", para("ContainerCheckinfoId"), para("SheetId"), para("TotalDuration"))
        DatabaseHelper.ExecuteSql(sb.ToString)
        InsertOiltoSheet = True
    End Function

    ''' <summary>
    ''' 查询工人本月工时（新生产报表页面） add zhunan 2023.2.13
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkerWorkTime(ByVal para As System.Collections.Generic.Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSStepTimeIBusiness.GetWorkerWorkTime
        Dim sql = <string>
SELECT t.employeeid,NVL(round(SUM(t.employeeWorktime)/60,2),0) worktime
FROM (
SELECT NVL(wts.workerid,e.employeeid) employeeid,NVL(e2.fullname,e.fullname) fullname, NVL(e2.employeename,e.employeename) employeename,
       (CASE WHEN wts.containersteptimesheetid IS NOT NULL THEN NVL(wts.splitduration,0)  
             WHEN wts.containersteptimesheetid IS NULL AND (wt.adjustdate IS NOT NULL OR wt.verificationdate IS NOT NULL) THEN NVL(wt.totalduration,0)
             WHEN wts.containersteptimesheetid IS NULL AND wt.adjustdate IS NULL AND wt.verificationdate IS  NULL THEN ROUND((sd.durationperunit * (wt.qty-wt.scrapqty)) / NVL(wt.splitcount,1),2) END
       ) employeeWorktime
FROM containersteptimesheet wt
LEFT JOIN employee e ON e.employeeid = wt.workerid
INNER JOIN containercurrentinfo cci ON cci.containerid = wt.containerid
LEFT JOIN stepschedulingdetail sd ON sd.stepid = wt.workflowstepid
LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
LEFT JOIN employee e2 ON e2.employeeid = wts.workerid
WHERE wt.workerid = '{0}' AND {1}
) t
GROUP BY t.employeeid</string>.Value
        Dim query As String = String.Format(" wt.checkdate >= TO_DATE('{0}','yyyy-mm-dd hh24:mi:ss') AND wt.checkdate <= TO_DATE('{1}','yyyy-mm-dd hh24:mi:ss') ", para("ReportBdate"), para("ReportEdate"))
        sql = String.Format(sql, para("WorkID"), query)
        Return DatabaseHelper.Query_ReturnDatTable(sql)
    End Function
#Region "装试 获取所有工时 补充工步"
    ''' <summary>
    ''' 获取没有工步的工序列表yuxj20230324
    ''' ’总体补充工步功能使用,暂时放在页面，后续移入类中
    ''' </summary>
    ''' <param name="workflowid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ZSGetStepNoStepinfo(ByVal workflowid As String) As DataTable Implements IBusiness.uLMSStepTimeIBusiness.ZSGetStepNoStepinfo
        Dim strsql As String =
            <string>
                    select wfs.workflowid,wfs.workflowstepname,wfs.workflowstepname stepinfodesc,RAWTOHEX(sys_guid()) STEPINFONAME,wfs.workflowstepid,
                            1 Revision,1 STEPNO,'Add' EditFlag
                    from  workflowstep wfs
                    left join workflow wf on wfs.workflowid=wf.workflowid                     
                    left join stepinfo sf on sf.workflowstepid=wfs.workflowstepid
                    where sf.workflowstepid is null
                </string>
        strsql = strsql + " and wfs.workflowid='" + workflowid + "'"
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function
    'Add by Yuxj20200429 获取全部工时
    Function GetZSAllWorkTimeData(ByVal para As Dictionary(Of String, String), ByRef totalStepTimeValue As Double) As uMESProcessResult Implements uLMSStepTimeIBusiness.GetZSAllWorkTimeData
        Dim sb As New StringBuilder
        Dim strsql As String = ""
        Dim i_dec As Decimal = Convert.ToDecimal(para("JX"))
        sb.AppendLine("select fullname,employeename,sum(totalduration) TotalDuration ")
        sb.AppendFormat(" ,sum(totalduration*{0}) TotalJX ", i_dec) '增加绩效列

        sb.AppendLine(" from (")
        '批产，施工工时
        sb.AppendLine(" select fullname,employeename,totalduration from PICHAN_WORKTIME_VIEW pv where 1=1 ")
        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat("AND pv.attributevalue like '%{0}%'", para("MachineStart"))
        End If

        If para.ContainsKey("MachineStatus") Then
            sb.AppendFormat("AND pv.workflowstepid='{0}'", para("MachineStatus"))
        End If

        '工人登陆，显示工人所有工区的信息
        If para.ContainsKey("WorkID") Then
            sb.AppendFormat("AND pv.employeeid='{0}'", para("WorkID"))
        Else
            If para.Keys.Contains("workcenterID") Then
                sb.AppendFormat(" AND pv.WORKCENTERID ='{0}'", para("workcenterID"))
            End If

            If para.Keys.Contains("shiftID") Then
                sb.AppendFormat(" AND pv.shiftID ='{0}'", para("shiftID"))
            End If

            If para.Keys.Contains("teamID") Then
                sb.AppendFormat(" AND pv.teamID ='{0}'", para("teamID"))
            End If
        End If
        If para.ContainsKey("ReportBdate") Then
            sb.AppendFormat(" and pv.createdate>=to_date('{0}','yyyy/mm/dd')", para("ReportBdate"))
            If para.ContainsKey("ReportEdate") Then
                sb.AppendFormat(" and pv.createdate<to_date('{0}','yyyy/mm/dd')+1", para("ReportEdate"))
            End If
        End If
        sb.AppendLine(" union all ")
        '油封工时
        strsql = <string>
                    SELECT e.fullname,e.employeename,nvl(trunc(os.oilsealdurationperunit/NVL(ec.WorkerCount,1),2),0) totalduration 
                        FROM enginepartoilseal es                        
                        LEFT JOIN oilsealcycleinfo os ON es.oilsealcycleinfoid=os.oilsealcycleinfoid
                        INNER JOIN enginepartoilsealworker ew ON es.enginepartoilsealid=ew.enginepartoilsealid
                        LEFT JOIN 
                        (SELECT ew.enginepartoilsealid,COUNT(1) WorkerCount  
                        FROM enginepartoilsealworker ew GROUP BY ew.enginepartoilsealid) ec ON ec.enginepartoilsealid=es.enginepartoilsealid
                        LEFT JOIN EMPLOYEE E  ON E. EMPLOYEEID =  ew.employeeid
                        LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid
                        LEFT JOIN workcenter wc ON wc.workcenterid = sv.workcenterid
                        LEFT JOIN shift SS ON ss.shiftid=e.shiftid
                        LEFT JOIN teamemployee te ON te.employeeid=e.employeeid
                        LEFT JOIN team t ON t.teamid=te.teamid 
                        WHERE 1=1          
               </string>
        sb.AppendLine(strsql)
        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat("AND es.engineno like '%{0}%'", para("MachineStart"))
        End If

        '工人登陆，显示工人所有工区的信息
        If para.ContainsKey("WorkID") Then
            sb.AppendFormat("AND ew.employeeid='{0}'", para("WorkID"))
        Else
            If para.Keys.Contains("workcenterID") Then
                sb.AppendFormat(" AND sv.WORKCENTERID ='{0}'", para("workcenterID"))
            End If

            If para.Keys.Contains("shiftID") Then
                sb.AppendFormat(" AND ss.shiftID ='{0}'", para("shiftID"))
            End If

            If para.Keys.Contains("teamID") Then
                sb.AppendFormat(" AND t.teamID ='{0}'", para("teamID"))
            End If
        End If

        If para.ContainsKey("ReportBdate") Then
            sb.AppendFormat(" and es.createdate>=to_date('{0}','yyyy/mm/dd')", para("ReportBdate"))
            If para.ContainsKey("ReportEdate") Then
                sb.AppendFormat(" and es.createdate<to_date('{0}','yyyy/mm/dd')+1", para("ReportEdate"))
            End If
        End If
        '试车工时
        sb.AppendLine(" union all ")
        strsql = <string>
                    SELECT TDM.FULLNAME,TDM.EMPLOYEENAME,TDM.DURATIONQTY totalduration                                      
                    FROM TDM_STEPTIMESHEET TDM
                    LEFT JOIN employee e ON e.employeename=tdm.employeename
                    LEFT JOIN Sessionvalues sv ON sv.sessionvaluesid=e.sessionvaluesid
                    LEFT JOIN WORKCENTER WC ON wc.workcenterid=sv.workcenterid 
                    LEFT JOIN TEAMEMPLOYEE TE ON TE.EMPLOYEEID=E.EMPLOYEEID
                    LEFT JOIN TEAM T ON T.TEAMID=TE.TEAMID
                    LEFT JOIN SHIFT SS ON SS.SHIFTID=T.SHIFTID
                    LEFT JOIN WORKCENTER WCC ON WCC.WORKCENTERID=SS.WORKCENTERID 
                    where 1=1
               </string>
        sb.AppendLine(strsql)
        If para.ContainsKey("MachineStart") Then
            sb.AppendFormat("AND TDM.engineno like '%{0}%'", para("MachineStart"))
        End If

        '工人登陆，显示工人所有工区的信息
        If para.ContainsKey("WorkID") Then
            sb.AppendFormat("AND ew.employeeid='{0}'", para("WorkID"))
        Else
            If para.Keys.Contains("workcenterID") Then
                sb.AppendFormat(" AND sv.WORKCENTERID ='{0}'", para("workcenterID"))
            End If

            If para.Keys.Contains("shiftID") Then
                sb.AppendFormat(" AND ss.shiftID ='{0}'", para("shiftID"))
            End If

            If para.Keys.Contains("teamID") Then
                sb.AppendFormat(" AND t.teamID ='{0}'", para("teamID"))
            End If
        End If

        If para.ContainsKey("ReportBdate") Then
            sb.AppendFormat(" and TDM.Completedate>=to_date('{0}','yyyy/mm/dd')", para("ReportBdate"))
            If para.ContainsKey("ReportEdate") Then
                sb.AppendFormat(" and TDM.Completedate<to_date('{0}','yyyy/mm/dd')+1", para("ReportEdate"))
            End If
        End If
        sb.AppendLine(" ) group by fullname,employeename ")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetZSAllWorkTimeData = DatabaseHelper.RunProcedureForPage(paraPage)

        Dim strTotalSql = "select nvl(sum(nvl(TotalDuration,0)),0) TotalDuration from (" + sb.ToString + ") "
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strTotalSql)
        If dt.Rows.Count > 0 Then
            totalStepTimeValue = dt.Rows(0)("TotalDuration").ToString
        Else
            totalStepTimeValue = 0
        End If


    End Function
#End Region
End Class
